package com.dict.classes;

import java.sql.*;

public class WordDb {
		//String connectionURL = "jdbc:mysql://linux16.markum.net/dashboard";
	    String connectionURL = "jdbc:mysql://localhost/dashboard";
		Connection connection = null;
		Statement statement = null;
	
	public WordDb() throws Exception {
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		//connection = DriverManager.getConnection(connectionURL,"monster","magnet");
		connection = DriverManager.getConnection(connectionURL,"root","111111");
	}

	public void disconnect() throws Exception
	{
		connection.close();
	}
	
	public ResultSet loadById(int id) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;

		String queryStr = "select * from words where id = " + id;
		
		stmt = connection.prepareStatement(queryStr);
		rs = stmt.executeQuery();
		return rs;
	}
	
	public int addWord(Word word) {
		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			connection = DriverManager.getConnection(connectionURL,"root","111111");
		}
		catch(Exception e){
			e.printStackTrace();
		}

		PreparedStatement stmt;
		String queryStr = "INSERT INTO words (word,meaning,userId) VALUES(?,?,?)";

		try
		{
			stmt = connection.prepareStatement(queryStr);
			stmt.setString(1, word.getWord());
			stmt.setString(2, word.getMeaning());
			stmt.setInt(3, word.getUserId());
			stmt.executeUpdate();
		}
		catch (SQLException s){
			System.out.println("Error : "+s.toString());
		}
		return 1;
	}
	
	public int updateWord(Word word) throws Exception {
		PreparedStatement stmt;
		
		String queryStr = "UPDATE words SET word = ?, meaning = ? userId = ? where id= ?";
		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1, word.getWord());
		stmt.setString(2, word.getMeaning());
		stmt.setInt(3, word.getId());
		stmt.setInt(4, word.getUserId());
		try
		{
			stmt.executeUpdate();
		} catch (Exception e){
			System.out.println(e.getMessage());
			return 0;
		}
		return 1;
	}
	
	public boolean deleteWord(int id) throws Exception {
		PreparedStatement stmt;
		
		String queryStr = "DELETE FROM words where id=" + id;
		stmt = connection.prepareStatement(queryStr);
		try {
			stmt.executeUpdate();
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return false;
		}
		return true;
	}
	
	public ResultSet getAllWordsSet() throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
		
		String queryStr = "select * from words";
		
			
		stmt = connection.prepareStatement(queryStr);
		rs = stmt.executeQuery();
		return rs;
	}
	
	public ResultSet getUserNames(int [] userIds) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
		
		String strIds = "(";
		for(int i = 0; i < userIds.length; i++)
		{
			strIds += userIds[i] + ",";
		}
		strIds = strIds.substring(0, strIds.length() - 1);
		strIds += ")";
		String queryStr = "select id, username from users where id IN " + strIds;
		
			
		stmt = connection.prepareStatement(queryStr);
		rs = stmt.executeQuery();
		return rs;
	}
}